package org.upfrost.idiom;

import java.util.LinkedList;
import java.util.List;

import junit.framework.Assert;

import org.junit.Test;
import org.upfrost.idiom.GenericSQL92;
import org.upfrost.idiom.Limit;
import org.upfrost.idiom.Order;
import org.upfrost.util.ArrayUtils;


public class GenericSQL92Test 
{

	@Test
	public void testDefaulForceQuote()
	{
		GenericSQL92 sql = new GenericSQL92();
		Assert.assertEquals(false, sql.isForceQuoteColumnIdentifiers());
	}
	
	@Test
	public void testIdentifierNormalizer()
	{
		GenericSQL92 sql = new GenericSQL92();

		String ident1 = "rezkrl"; // random
		Assert.assertEquals(ident1, sql.normalizeColumnIdentifier(ident1));
		
		Assert.assertEquals("\"select\"", sql.normalizeColumnIdentifier("select"));
		Assert.assertEquals("\"when\"", sql.normalizeColumnIdentifier("when"));
		Assert.assertEquals("\"update\"", sql.normalizeColumnIdentifier("update"));
	}
	
	@Test
	public void testIdentifierNormalizerForceQuote()
	{
		GenericSQL92 sql = new GenericSQL92();
		sql.setForceQuoteColumnIdentifiers(true);
		String ident1 = "rezkrl"; // random

		Assert.assertEquals("\"select\"", sql.normalizeColumnIdentifier("select"));
		Assert.assertEquals("\"when\"", sql.normalizeColumnIdentifier("when"));
		Assert.assertEquals("\"update\"", sql.normalizeColumnIdentifier("update"));

		Assert.assertEquals("\""+ident1+"\"", sql.normalizeColumnIdentifier(ident1));
		Assert.assertEquals("\"testtest\"", sql.normalizeColumnIdentifier("testtest"));
	}

	@Test
	public void testInsertStatement()
	{
		GenericSQL92 sql = new GenericSQL92();

		// No quotes
		String insert = sql.createInsertStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"));
		Assert.assertTrue(insert.equalsIgnoreCase("INSERT INTO sometable (col1,col2,col3) values (?,?,?)"));
		
		// quoted tablename
		insert = sql.createInsertStatement("when", ArrayUtils.asList("col1", "col2", "col3"));
		Assert.assertTrue(insert.equalsIgnoreCase("INSERT INTO \"when\" (col1,col2,col3) values (?,?,?)"));

		// quoted column
		insert = sql.createInsertStatement("sometable", ArrayUtils.asList("when", "execute", "col3"));
		Assert.assertTrue(insert.equalsIgnoreCase("INSERT INTO sometable (\"when\",\"execute\",col3) values (?,?,?)"));
		
		// quoted table and column
		insert = sql.createInsertStatement("goto", ArrayUtils.asList("when", "execute", "col3"));
		Assert.assertTrue(insert.equalsIgnoreCase("INSERT INTO \"goto\" (\"when\",\"execute\",col3) values (?,?,?)"));
		
		// No quotes
		insert = sql.createInsertStatement("sometable", ArrayUtils.asList("col1"));
		Assert.assertTrue(insert.equalsIgnoreCase("INSERT INTO sometable (col1) values (?)"));
	}

	@Test
	public void testUpdateStatement()
	{
		GenericSQL92 sql = new GenericSQL92();

		// No quotes
		String update = sql.createUpdateStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"));
		Assert.assertTrue(update.equalsIgnoreCase("update sometable SET col1=?,col2=?,col3=? where col1=? and col4=?"));

		// quoted tablename
		update = sql.createUpdateStatement("when", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1"));
		Assert.assertTrue(update.equalsIgnoreCase("update \"when\" SET col1=?,col2=?,col3=? where col1=?"));

		// quoted columns
		update = sql.createUpdateStatement("sometable", ArrayUtils.asList("select", "update"),  ArrayUtils.asList("insert"));
		Assert.assertTrue(update.equalsIgnoreCase("update sometable SET \"select\"=?,\"update\"=? where \"insert\"=?"));

		// empty where clause
		update = sql.createUpdateStatement("sometable", ArrayUtils.asList("select", "col1"),  null);
		Assert.assertTrue(update.equalsIgnoreCase("update sometable SET \"select\"=?,col1=?"));

	}
	
	@Test 
	public void testSelectStatement()
	{
		GenericSQL92 sql = new GenericSQL92();
		
		// No quotes, no order, no limit
		String select = sql.createSelectStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select col1,col2,col3 from sometable where col1=? and col4=?"));
		
		// Quoted tablename, no order, no limit
		select = sql.createSelectStatement("when", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select col1,col2,col3 from \"when\" where col1=? and col4=?"));
		
		// Quoted tablename, no where, no order, no limit
		select = sql.createSelectStatement("when", ArrayUtils.asList("col1", "col2", "col3"),  null, null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select col1,col2,col3 from \"when\""));

		// NonQuoted tablename, star, no where, no order, no limit
		select = sql.createSelectStatement("sometable", null,  null, null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable"));

		// NonQuoted tablename, star, empty where, no order, no limit
		select = sql.createSelectStatement("sometable", null,  new LinkedList<String>(), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable"));
	
		// NonQuoted tablename, empty select, empty where, no order, no limit
		select = sql.createSelectStatement("sometable", new LinkedList<String>(),  new LinkedList<String>(), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable"));

		// NonQuoted tablename, empty select, empty where, no order, no limit
		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1"), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=?"));
	
		// NonQuoted tablename, empty select, empty where, no order, no limit
		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2"), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=?"));

		// NonQuoted tablename, empty select, empty where, no order, no limit
		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2", "col3"), null, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=? and col3=?"));

	}

	public void testSelectStatementWithOrder()
	{
		GenericSQL92 sql = new GenericSQL92();
		
		List<Order> olist1a = ArrayUtils.asList(new Order("col1", true));
		List<Order> olist1d = ArrayUtils.asList(new Order("col1", false));
		
		List<Order> olist2a = ArrayUtils.asList(new Order("col1", true), new Order("col2", false));
		List<Order> olist2d = ArrayUtils.asList(new Order("col1", false), new Order("col2", true));

		List<Order> olist3a = ArrayUtils.asList(new Order("col1", true), new Order("col2", false), new Order("col3", true));
		List<Order> olist3d = ArrayUtils.asList(new Order("col1", false), new Order("col2", true), new Order("col3", false));

		String select = sql.createSelectStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"), olist1a, null);
		Assert.assertTrue(select.equalsIgnoreCase("select col1,col2,col3 from sometable where col1=? and col4=? order by col1 asc"));

		select = sql.createSelectStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"), olist1d, null);
		Assert.assertTrue(select.equalsIgnoreCase("select col1,col2,col3 from sometable where col1=? and col4=? order by col1 desc"));

		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2", "col3"), olist2a, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=? and col3=? order by col1 asc, col2 desc"));
		
		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2", "col3"), olist2d, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=? and col3=? order by col1 desc, col2 asc"));

		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2", "col3"), olist3a, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=? and col3=? order by col1 asc, col2 desc, col3 asc"));
		
		select = sql.createSelectStatement("sometable", new LinkedList<String>(), ArrayUtils.asList("col1","col2", "col3"), olist3d, null);
		Assert.assertTrue(select.equalsIgnoreCase("select * from sometable where col1=? AND col2=? and col3=? order by col1 desc, col2 asc, col3 desc"));
	}

	public void testSelectStatementWithLimit()
	{
		GenericSQL92 sql = new GenericSQL92();

		List<Order> olist1a = ArrayUtils.asList(new Order("col1", true));
		List<Order> olist1d = ArrayUtils.asList(new Order("col1", false));
		
		Limit limit0 = new Limit(0);
		Limit limit10 = new Limit(10);
		
		try {
			String select = sql.createSelectStatement("sometable", ArrayUtils.asList("col1", "col2", "col3"),  ArrayUtils.asList("col1", "col4"), olist1a, limit0);
			Assert.fail("Using Limit should raise an exception here");
		} catch (Exception e)
		{
			// OK
		}

		try {
			String select = sql.createSelectStatement("sometable", null,  null, null, limit10);
			Assert.fail("Using Limit should raise an exception here");
		} catch (Exception e)
		{
			// OK
		}

	}
}
